import scrapy
import sqlite3

conn = sqlite3.connect('C:/Work/sqlite/data/menu/menu_data.db')
c = conn.cursor()


class MenuSpider(scrapy.Spider):
    name = "menu"

    def start_requests(self):
        urls = [
            'https://home.meishichina.com/recipe/recai/hot/',
            'https://home.meishichina.com/recipe/liangcai/hot/',
            'https://home.meishichina.com/recipe/tanggeng/hot/',
            'https://home.meishichina.com/recipe/zhushi/hot/',
            'https://home.meishichina.com/recipe/xiaochi/hot/',
            'https://home.meishichina.com/recipe/xican/hot/',
            'https://home.meishichina.com/recipe/hongbei/hot/',
            'https://home.meishichina.com/recipe/yinpin/hot/',
            'https://home.meishichina.com/recipe/jiangpaoyancai/hot/',
            'https://home.meishichina.com/recipe/zizhishicai/hot/',
        ]
        #yield scrapy.Request(url=urls[0], callback=self.parse)
        for url in urls:
            yield scrapy.Request(url=url, callback=self.parse)

    def parse(self, response):
        cate = response.url.split("/")[4]
        # 抓取详情页面链接
        for li in response.css('#J_list ul li'):
            href = li.css('.pic a').attrib['href']
            yield scrapy.Request(url=href, meta={'cate': cate}, callback=self.parseDetail)
            #break
        # 处理下一页
        next_page = response.xpath('//div[@class="ui-page-inner"]/a[contains(text(), "下一页")]')
        if next_page:
            next_page = next_page[0].attrib['href']
            if int(next_page.split('/')[-2]) <= 300: #只抓300页
                yield response.follow(next_page, callback=self.parse)

    def parseDetail(self, response):
        # 基本信息
        cate = response.meta['cate']
        title = response.css('#recipe_title::text').get()
        cover = response.css('#recipe_De_imgBox img')[0].attrib['src']
        intro = response.css('#block_txt1::text').get(default="")
        taste = response.xpath('//li/span[@class="category_s2"][contains(text(), "口味")]/parent::li').xpath(
            'child::span[@class="category_s1"]/a/text()').get()
        craft = response.xpath('//li/span[@class="category_s2"][contains(text(), "工艺")]/parent::li').xpath(
            'child::span[@class="category_s1"]/a/text()').get()
        time = response.xpath('//li/span[@class="category_s2"][contains(text(), "耗时")]/parent::li').xpath(
            'child::span[@class="category_s1"]/a/text()').get()
        difficulty = response.xpath('//li/span[@class="category_s2"][contains(text(), "难度")]/parent::li').xpath(
            'child::span[@class="category_s1"]/a/text()').get()
        tips = response.xpath('//div[@class="recipeTip"][1]/text()').re(r'\S+')
        if tips is None:
            tips = ''
        else:
            tips = '\n'.join((str(x) for x in tips))
        # 写入数据
        c.execute(f"insert into menu (cate,name,cover,intro,url,tips,taste,craft,time,difficulty) \
         values ('{cate}','{title}','{cover}','{intro}','{response.url}','{tips}','{taste}','{craft}','{time}','{difficulty}')")
        baseId = c.lastrowid
        # 配料-主料
        for zl in response.xpath(
                '//fieldset[@class="particulars"]/legend[contains(text(), "主料")]/parent::fieldset').css('ul li'):
            zlname = zl.css('.category_s1 a b::text').get()
            if (zlname is None):
                zlname = zl.css('.category_s1 b::text').get()
            zlnum = zl.css('.category_s2::text').get()
            # 查询配料id
            cursor = c.execute(f"select id from burden where cate = 1 and name = '{zlname}'")
            result = cursor.fetchone()
            if result:
                bid = result[0]
            else:
                c.execute(f"insert into burden (cate, name) values('1', '{zlname}')")
                bid = c.lastrowid
            # 写菜单主料
            c.execute(f"insert into menu_burden (mid, bid, cate, num) values('{baseId}', '{bid}', '1', '{zlnum}')")
        # 配料-辅料
        for fl in response.xpath(
                '//fieldset[@class="particulars"]/legend[contains(text(), "辅料")]/parent::fieldset').css('ul li'):
            flname = fl.css('.category_s1 a b::text').get()
            if (flname is None):
                flname = fl.css('.category_s1 b::text').get()
            flnum = fl.css('.category_s2::text').get()
            # 查询配料id
            cursor = c.execute(f"select id from burden where cate = 2 and name = '{flname}'")
            result = cursor.fetchone()
            if result:
                bid = result[0]
            else:
                c.execute(f"insert into burden (cate, name) values('2', '{flname}')")
                bid = c.lastrowid
            # 写菜单主料
            c.execute(f"insert into menu_burden (mid, bid, cate, num) values('{baseId}', '{bid}', '2', '{flnum}')")
        # 配料-调料
        for tl in response.xpath(
                '//fieldset[@class="particulars"]/legend[contains(text(), "调料")]/parent::fieldset').css('ul li'):
            tlname = tl.css('.category_s1 a b::text').get()
            if (tlname is None):
                tlname = tl.css('.category_s1 b::text').get()
            tlnum = tl.css('.category_s2::text').get()
            # 查询配料id
            cursor = c.execute(f"select id from burden where cate = 3 and name = '{tlname}'")
            result = cursor.fetchone()
            if result:
                bid = result[0]
            else:
                c.execute(f"insert into burden (cate, name) values('3', '{tlname}')")
                bid = c.lastrowid
            # 写菜单主料
            c.execute(f"insert into menu_burden (mid, bid, cate, num) values('{baseId}', '{bid}', '3', '{tlnum}')")
        # 步骤
        for step in response.css('.recipeStep ul li'):
            img = step.css('.recipeStep_img img')[0].attrib['src']
            intro = step.css('.recipeStep_word::text').get()
            stepNum = step.css('.recipeStep_word .recipeStep_num::text').get()
            c.execute(f"insert into step (mid, cover, intro, step) values('{baseId}', '{img}', '{intro}', '{stepNum}')")

        # 提交事物
        conn.commit()
        return
